package model.database;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Observable;
import java.util.Properties;

public class DatabaseConnection extends Observable {
	private final int statusCodeAuctionEnded = 5;

	private Connection connection;

	public DatabaseConnection() {
		connection = null;
	}

	public void connect() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Properties properties = new Properties();
			properties.put("useUnicode", "true");
			properties.put("characterEncoding", "UTF-8");
			connection = DriverManager
					.getConnection("jdbc:mysql://localhost/tradingonrails?"
							+ "user=tradingonrails&password=tradingonrails", properties);
			setChanged();
			notifyObservers("Database connection established");
		} catch (SQLException ex) {
			setChanged();
			notifyObservers(ex);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public void disConnect() {
		try {
			connection.close();
			connection = null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void loadDatabaseContent(DatabaseContent databaseContent) {
		try {
			setChanged();
			ResultSet rs = getAdInfoResult(new CalendarWrapper());
			while (rs.next()) {
				//System.out.println("putting: "+getString(rs, "id"));
				databaseContent.putAd(getString(rs, "id"), getString(rs,
						"title")
						+ " " + getString(rs, "description"));
				databaseContent.putAdInCounty(getString(rs, "province_id"),
						getString(rs, "county_id"), getString(rs, "id"));
				databaseContent.putAdInProvince(getString(rs, "province_id"),
						getString(rs, "id"));
			}

			rs = getAdCategoriesResultSet();
			while (rs.next()) {
				databaseContent.putAdInCategory(getString(rs, "category_id"),
						getString(rs, "ad_id"), getString(rs, "enddate"));
			}

			rs = getCategoriesResultSet();
			while (rs.next()) {
				databaseContent.putSubCategoryInCategory(getString(rs,
						"category_id"), getString(rs, "id"));
				databaseContent.setCategoryOfSubCategory(getString(rs, "id"),
						getString(rs, "category_id"));
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public ResultSet getAdInfoResult(CalendarWrapper endingAfter)
			throws SQLException {
		notifyObservers("Requesting all ads ending after " + endingAfter);
		return executeQuery("SELECT ads.id, ads.title, ads.description, users.county_id, counties.province_id FROM ads INNER JOIN users ON users.id=ads.user_id INNER JOIN counties ON counties.id=users.county_id "
				+ " WHERE ads.enddate > '"
				+ endingAfter
				+ "' AND ads.ad_status_id != " + statusCodeAuctionEnded);
	}

	public ResultSet getAdCategoriesResultSet() throws SQLException {
		return executeQuery("SELECT ad_categories.ad_id, ad_categories.category_id, ads.enddate FROM ad_categories INNER JOIN ads ON ads.id=ad_categories.ad_id");
	}

	public ResultSet getCategoriesResultSet() throws SQLException {
		// SELECT id, category_id FROM categories
		return executeQuery("SELECT categories.category_id, categories.id as id FROM ad_categories RIGHT OUTER JOIN categories ON categories.id=ad_categories.category_id");
	}

	public ResultSet executeQuery(String string) throws SQLException {
		//System.out.println(string);
		Statement stmt = connection.createStatement();
		ResultSet rs;
		rs = stmt.executeQuery(string);
		return rs;
	}

	public void executeUpdate(String string) throws SQLException {
		Statement stmt = connection.createStatement();
		stmt.executeUpdate(string);
	}

	public String getString(ResultSet result, String column) {
		String str = new String();
		try {
			InputStreamReader in = new InputStreamReader(result
					.getAsciiStream(column));
			while (in.ready())
				str = str + (char) in.read();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			return str;
		}
	}
}
